{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame                       Country  CountryID  Continent  \\\n",
      "0                 Afghanistan          1          1   \n",
      "1                     Albania          2          2   \n",
      "2                     Algeria          3          3   \n",
      "3                     Andorra          4          2   \n",
      "4                      Angola          5          3   \n",
      "5         Antigua and Barbuda          6          4   \n",
      "6                   Argentina          7          5   \n",
      "7                     Armenia          8          2   \n",
      "8                   Australia          9          6   \n",
      "9                     Austria         10          2   \n",
      "10                 Azerbaijan         11          2   \n",
      "11                    Bahamas         12          4   \n",
      "12                    Bahrain         13          1   \n",
      "13                 Bangladesh         14          7   \n",
      "14                   Barbados         15          4   \n",
      "15                    Belarus         16          2   \n",
      "16                    Belgium         17          2   \n",
      "17                     Belize         18          5   \n",
      "18                      Benin         19          3   \n",
      "19                    Bermuda         20          4   \n",
      "20                     Bhutan         21          7   \n",
      "21                    Bolivia         22          5   \n",
      "22     Bosnia and Herzegovina         23          2   \n",
      "23                   Botswana         24          3   \n",
      "24                     Brazil         25          5   \n",
      "25          Brunei Darussalam         26          6   \n",
      "26                   Bulgaria         27          2   \n",
      "27               Burkina Faso         28          3   \n",
      "28                    Burundi         29          3   \n",
      "29                   Cambodia         30          7   \n",
      "..                        ...        ...        ...   \n",
      "172                 Swaziland        173          3   \n",
      "173                    Sweden        174          2   \n",
      "174               Switzerland        175          2   \n",
      "175                     Syria        176          1   \n",
      "176                    Taiwan        177          6   \n",
      "177                Tajikistan        178          2   \n",
      "178                  Tanzania        179          3   \n",
      "179                  Thailand        180          6   \n",
      "180               Timor-Leste        181          6   \n",
      "181                      Togo        182          3   \n",
      "182                     Tonga        183          6   \n",
      "183       Trinidad and Tobago        184          5   \n",
      "184                   Tunisia        185          1   \n",
      "185                    Turkey        186          2   \n",
      "186              Turkmenistan        187          2   \n",
      "187                    Tuvalu        188          6   \n",
      "188                    Uganda        189          3   \n",
      "189                   Ukraine        190          2   \n",
      "190      United Arab Emirates        191          1   \n",
      "191            United Kingdom        192          2   \n",
      "192  United States of America        193          4   \n",
      "193                   Uruguay        194          5   \n",
      "194                Uzbekistan        195          2   \n",
      "195                   Vanuatu        196          6   \n",
      "196                 Venezuela        197          5   \n",
      "197                   Vietnam        198          6   \n",
      "198        West Bank and Gaza        199          1   \n",
      "199                     Yemen        200          1   \n",
      "200                    Zambia        201          3   \n",
      "201                  Zimbabwe        202          3   \n",
      "\n",
      "     Adolescent fertility rate (%)  Adult literacy rate (%)  \\\n",
      "0                            151.0                     28.0   \n",
      "1                             27.0                     98.7   \n",
      "2                              6.0                     69.9   \n",
      "3                              NaN                      NaN   \n",
      "4                            146.0                     67.4   \n",
      "5                              NaN                      NaN   \n",
      "6                             62.0                     97.2   \n",
      "7                             30.0                     99.4   \n",
      "8                             16.0                      NaN   \n",
      "9                             14.0                      NaN   \n",
      "10                            31.0                     98.8   \n",
      "11                            46.0                      NaN   \n",
      "12                            14.0                     86.5   \n",
      "13                           135.0                     47.5   \n",
      "14                            51.0                      NaN   \n",
      "15                            22.0                     99.6   \n",
      "16                             5.0                      NaN   \n",
      "17                            90.0                     70.3   \n",
      "18                           108.0                     34.7   \n",
      "19                             NaN                      NaN   \n",
      "20                            62.0                      NaN   \n",
      "21                            97.0                     86.7   \n",
      "22                            25.0                     96.7   \n",
      "23                            51.0                     81.2   \n",
      "24                            71.0                     88.6   \n",
      "25                            31.0                     92.7   \n",
      "26                            40.0                     98.2   \n",
      "27                           131.0                     23.6   \n",
      "28                            30.0                     59.3   \n",
      "29                            52.0                     73.6   \n",
      "..                             ...                      ...   \n",
      "172                           73.0                     79.6   \n",
      "173                            7.0                      NaN   \n",
      "174                            5.0                      NaN   \n",
      "175                           58.0                     80.8   \n",
      "176                            NaN                      NaN   \n",
      "177                           57.0                     99.5   \n",
      "178                          139.0                     69.4   \n",
      "179                           70.0                     92.6   \n",
      "180                           49.0                      NaN   \n",
      "181                           89.0                     53.2   \n",
      "182                           17.0                     98.9   \n",
      "183                           35.0                     98.5   \n",
      "184                            8.0                     74.3   \n",
      "185                           51.0                     87.4   \n",
      "186                           29.0                     98.8   \n",
      "187                            NaN                      NaN   \n",
      "188                          159.0                     68.1   \n",
      "189                           29.0                     99.4   \n",
      "190                           37.0                     88.5   \n",
      "191                           27.0                      NaN   \n",
      "192                           43.0                      NaN   \n",
      "193                           64.0                     96.8   \n",
      "194                           40.0                      NaN   \n",
      "195                           92.0                     75.5   \n",
      "196                           81.0                     93.0   \n",
      "197                           25.0                     90.3   \n",
      "198                            NaN                      NaN   \n",
      "199                           83.0                     54.1   \n",
      "200                          161.0                     68.0   \n",
      "201                          101.0                     89.5   \n",
      "\n",
      "     Gross national income per capita (PPP international $)  \\\n",
      "0                                                  NaN        \n",
      "1                                               6000.0        \n",
      "2                                               5940.0        \n",
      "3                                                  NaN        \n",
      "4                                               3890.0        \n",
      "5                                              15130.0        \n",
      "6                                              11670.0        \n",
      "7                                               4950.0        \n",
      "8                                              33940.0        \n",
      "9                                              36040.0        \n",
      "10                                              5430.0        \n",
      "11                                                 NaN        \n",
      "12                                             34310.0        \n",
      "13                                              1230.0        \n",
      "14                                             15150.0        \n",
      "15                                              9700.0        \n",
      "16                                             33860.0        \n",
      "17                                              7080.0        \n",
      "18                                              1250.0        \n",
      "19                                                 NaN        \n",
      "20                                              4000.0        \n",
      "21                                              3810.0        \n",
      "22                                              6780.0        \n",
      "23                                             11730.0        \n",
      "24                                              8700.0        \n",
      "25                                             49900.0        \n",
      "26                                             10270.0        \n",
      "27                                              1130.0        \n",
      "28                                               320.0        \n",
      "29                                              1550.0        \n",
      "..                                                 ...        \n",
      "172                                             4700.0        \n",
      "173                                            34310.0        \n",
      "174                                            40840.0        \n",
      "175                                             4110.0        \n",
      "176                                                NaN        \n",
      "177                                             1560.0        \n",
      "178                                              980.0        \n",
      "179                                             7440.0        \n",
      "180                                             5100.0        \n",
      "181                                              770.0        \n",
      "182                                             5470.0        \n",
      "183                                            16800.0        \n",
      "184                                             6490.0        \n",
      "185                                             8410.0        \n",
      "186                                             3990.0        \n",
      "187                                                NaN        \n",
      "188                                              880.0        \n",
      "189                                             6110.0        \n",
      "190                                            31190.0        \n",
      "191                                            33650.0        \n",
      "192                                            44070.0        \n",
      "193                                             9940.0        \n",
      "194                                             2190.0        \n",
      "195                                             3480.0        \n",
      "196                                            10970.0        \n",
      "197                                             2310.0        \n",
      "198                                                NaN        \n",
      "199                                             2090.0        \n",
      "200                                             1140.0        \n",
      "201                                                NaN        \n",
      "\n",
      "     Net primary school enrolment ratio female (%)  \\\n",
      "0                                              NaN   \n",
      "1                                             93.0   \n",
      "2                                             94.0   \n",
      "3                                             83.0   \n",
      "4                                             49.0   \n",
      "5                                              NaN   \n",
      "6                                             98.0   \n",
      "7                                             84.0   \n",
      "8                                             97.0   \n",
      "9                                             98.0   \n",
      "10                                            83.0   \n",
      "11                                            89.0   \n",
      "12                                            98.0   \n",
      "13                                            90.0   \n",
      "14                                            96.0   \n",
      "15                                            88.0   \n",
      "16                                            97.0   \n",
      "17                                            97.0   \n",
      "18                                            73.0   \n",
      "19                                             NaN   \n",
      "20                                            79.0   \n",
      "21                                            95.0   \n",
      "22                                             NaN   \n",
      "23                                            85.0   \n",
      "24                                            95.0   \n",
      "25                                            94.0   \n",
      "26                                            92.0   \n",
      "27                                            42.0   \n",
      "28                                            73.0   \n",
      "29                                            89.0   \n",
      "..                                             ...   \n",
      "172                                           79.0   \n",
      "173                                           95.0   \n",
      "174                                           89.0   \n",
      "175                                           92.0   \n",
      "176                                            NaN   \n",
      "177                                           95.0   \n",
      "178                                           97.0   \n",
      "179                                           94.0   \n",
      "180                                           67.0   \n",
      "181                                           75.0   \n",
      "182                                           94.0   \n",
      "183                                           85.0   \n",
      "184                                           97.0   \n",
      "185                                           89.0   \n",
      "186                                            NaN   \n",
      "187                                            NaN   \n",
      "188                                            NaN   \n",
      "189                                           90.0   \n",
      "190                                           88.0   \n",
      "191                                           99.0   \n",
      "192                                           93.0   \n",
      "193                                          100.0   \n",
      "194                                           78.0   \n",
      "195                                           86.0   \n",
      "196                                           91.0   \n",
      "197                                           91.0   \n",
      "198                                            NaN   \n",
      "199                                           65.0   \n",
      "200                                           94.0   \n",
      "201                                           88.0   \n",
      "\n",
      "     Net primary school enrolment ratio male (%)  \\\n",
      "0                                            NaN   \n",
      "1                                           94.0   \n",
      "2                                           96.0   \n",
      "3                                           83.0   \n",
      "4                                           51.0   \n",
      "5                                            NaN   \n",
      "6                                           99.0   \n",
      "7                                           80.0   \n",
      "8                                           96.0   \n",
      "9                                           97.0   \n",
      "10                                          86.0   \n",
      "11                                          87.0   \n",
      "12                                          98.0   \n",
      "13                                          87.0   \n",
      "14                                          97.0   \n",
      "15                                          90.0   \n",
      "16                                          97.0   \n",
      "17                                          97.0   \n",
      "18                                          87.0   \n",
      "19                                           NaN   \n",
      "20                                          79.0   \n",
      "21                                          94.0   \n",
      "22                                           NaN   \n",
      "23                                          83.0   \n",
      "24                                          93.0   \n",
      "25                                          94.0   \n",
      "26                                          93.0   \n",
      "27                                          52.0   \n",
      "28                                          76.0   \n",
      "29                                          91.0   \n",
      "..                                           ...   \n",
      "172                                         78.0   \n",
      "173                                         95.0   \n",
      "174                                         89.0   \n",
      "175                                         97.0   \n",
      "176                                          NaN   \n",
      "177                                         99.0   \n",
      "178                                         98.0   \n",
      "179                                         94.0   \n",
      "180                                         70.0   \n",
      "181                                         86.0   \n",
      "182                                         97.0   \n",
      "183                                         85.0   \n",
      "184                                         96.0   \n",
      "185                                         93.0   \n",
      "186                                          NaN   \n",
      "187                                          NaN   \n",
      "188                                          NaN   \n",
      "189                                         90.0   \n",
      "190                                         88.0   \n",
      "191                                         98.0   \n",
      "192                                         91.0   \n",
      "193                                        100.0   \n",
      "194                                         79.0   \n",
      "195                                         88.0   \n",
      "196                                         91.0   \n",
      "197                                         96.0   \n",
      "198                                          NaN   \n",
      "199                                         85.0   \n",
      "200                                         90.0   \n",
      "201                                         87.0   \n",
      "\n",
      "     Population (in thousands) total  \n",
      "0                            26088.0  \n",
      "1                             3172.0  \n",
      "2                            33351.0  \n",
      "3                               74.0  \n",
      "4                            16557.0  \n",
      "5                               84.0  \n",
      "6                            39134.0  \n",
      "7                             3010.0  \n",
      "8                            20530.0  \n",
      "9                             8327.0  \n",
      "10                            8406.0  \n",
      "11                             327.0  \n",
      "12                             739.0  \n",
      "13                          155991.0  \n",
      "14                             293.0  \n",
      "15                            9742.0  \n",
      "16                           10430.0  \n",
      "17                             282.0  \n",
      "18                            8760.0  \n",
      "19                               NaN  \n",
      "20                             649.0  \n",
      "21                            9354.0  \n",
      "22                            3926.0  \n",
      "23                            1858.0  \n",
      "24                          189323.0  \n",
      "25                             382.0  \n",
      "26                            7693.0  \n",
      "27                           14359.0  \n",
      "28                            8173.0  \n",
      "29                           14197.0  \n",
      "..                               ...  \n",
      "172                           1134.0  \n",
      "173                           9078.0  \n",
      "174                           7455.0  \n",
      "175                          19408.0  \n",
      "176                              NaN  \n",
      "177                           6640.0  \n",
      "178                          39459.0  \n",
      "179                          63444.0  \n",
      "180                           1114.0  \n",
      "181                           6410.0  \n",
      "182                            100.0  \n",
      "183                           1328.0  \n",
      "184                          10215.0  \n",
      "185                          73922.0  \n",
      "186                           4899.0  \n",
      "187                             10.0  \n",
      "188                          29899.0  \n",
      "189                          46557.0  \n",
      "190                           4248.0  \n",
      "191                          60512.0  \n",
      "192                         302841.0  \n",
      "193                           3331.0  \n",
      "194                          26981.0  \n",
      "195                            221.0  \n",
      "196                          27191.0  \n",
      "197                          86206.0  \n",
      "198                              NaN  \n",
      "199                          21732.0  \n",
      "200                          11696.0  \n",
      "201                          13228.0  \n",
      "\n",
      "[202 rows x 9 columns]\n"
     ]
    }
   ],
   "source": [
    "from pandas.io.parsers import read_csv\n",
    "\n",
    "df = read_csv('WHO_first9cols.csv')\n",
    "print('DataFrame', df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape (202, 9)\n",
      "Length 202\n"
     ]
    }
   ],
   "source": [
    "print('Shape', df.shape)\n",
    "print('Length', len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column headers:\n",
      " Index(['Country', 'CountryID', 'Continent', 'Adolescent fertility rate (%)',\n",
      "       'Adult literacy rate (%)',\n",
      "       'Gross national income per capita (PPP international $)',\n",
      "       'Net primary school enrolment ratio female (%)',\n",
      "       'Net primary school enrolment ratio male (%)',\n",
      "       'Population (in thousands) total'],\n",
      "      dtype='object')\n",
      "Data types:\n",
      " Country                                                    object\n",
      "CountryID                                                   int64\n",
      "Continent                                                   int64\n",
      "Adolescent fertility rate (%)                             float64\n",
      "Adult literacy rate (%)                                   float64\n",
      "Gross national income per capita (PPP international $)    float64\n",
      "Net primary school enrolment ratio female (%)             float64\n",
      "Net primary school enrolment ratio male (%)               float64\n",
      "Population (in thousands) total                           float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print('Column headers:\\n', df.columns)\n",
    "print('Data types:\\n', df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index:\n",
      " RangeIndex(start=0, stop=202, step=1)\n"
     ]
    }
   ],
   "source": [
    "print(\"Index:\\n\", df.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 获取DataFrame中的数据，是Numpy的ndarray类型的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Values:\n",
      ": [['Afghanistan' 1 1 ... nan nan 26088.0]\n",
      " ['Albania' 2 2 ... 93.0 94.0 3172.0]\n",
      " ['Algeria' 3 3 ... 94.0 96.0 33351.0]\n",
      " ...\n",
      " ['Yemen' 200 1 ... 65.0 85.0 21732.0]\n",
      " ['Zambia' 201 3 ... 94.0 90.0 11696.0]\n",
      " ['Zimbabwe' 202 3 ... 88.0 87.0 13228.0]]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "numpy.ndarray"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "values = df.values\n",
    "print(\"Values:\\n:\", values)\n",
    "type(values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Series\n",
    "- 带标签的一维数据结构\n",
    "- 对DataFrame的某一列索引，会得到一个Series对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Type of country_col:\n",
      " <class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "country_col = df['Country']\n",
    "print(\"Type of country_col:\\n\", type(country_col))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape:\n",
      " (202,)\n",
      "Index:\n",
      " RangeIndex(start=0, stop=202, step=1)\n",
      "Values:\n",
      " ['Afghanistan' 'Albania' 'Algeria' 'Andorra' 'Angola'\n",
      " 'Antigua and Barbuda' 'Argentina' 'Armenia' 'Australia' 'Austria'\n",
      " 'Azerbaijan' 'Bahamas' 'Bahrain' 'Bangladesh' 'Barbados' 'Belarus'\n",
      " 'Belgium' 'Belize' 'Benin' 'Bermuda' 'Bhutan' 'Bolivia'\n",
      " 'Bosnia and Herzegovina' 'Botswana' 'Brazil' 'Brunei Darussalam'\n",
      " 'Bulgaria' 'Burkina Faso' 'Burundi' 'Cambodia' 'Cameroon' 'Canada'\n",
      " 'Cape Verde' 'Central African Republic' 'Chad' 'Chile' 'China' 'Colombia'\n",
      " 'Comoros' 'Congo, Dem. Rep.' 'Congo, Rep.' 'Cook Islands' 'Costa Rica'\n",
      " \"Cote d'Ivoire\" 'Croatia' 'Cuba' 'Cyprus' 'Czech Republic' 'Denmark'\n",
      " 'Djibouti' 'Dominica' 'Dominican Republic' 'Ecuador' 'Egypt'\n",
      " 'El Salvador' 'Equatorial Guinea' 'Eritrea' 'Estonia' 'Ethiopia' 'Fiji'\n",
      " 'Finland' 'France' 'French Polynesia' 'Gabon' 'Gambia' 'Georgia'\n",
      " 'Germany' 'Ghana' 'Greece' 'Grenada' 'Guatemala' 'Guinea' 'Guinea-Bissau'\n",
      " 'Guyana' 'Haiti' 'Honduras' 'Hong Kong, China' 'Hungary' 'Iceland'\n",
      " 'India' 'Indonesia' 'Iran (Islamic Republic of)' 'Iraq' 'Ireland'\n",
      " 'Israel' 'Italy' 'Jamaica' 'Japan' 'Jordan' 'Kazakhstan' 'Kenya'\n",
      " 'Kiribati' 'Korea, Dem. Rep.' 'Korea, Rep.' 'Kuwait' 'Kyrgyzstan'\n",
      " \"Lao People's Democratic Republic\" 'Latvia' 'Lebanon' 'Lesotho' 'Liberia'\n",
      " 'Libyan Arab Jamahiriya' 'Lithuania' 'Luxembourg' 'Macao, China'\n",
      " 'Macedonia' 'Madagascar' 'Malawi' 'Malaysia' 'Maldives' 'Mali' 'Malta'\n",
      " 'Marshall Islands' 'Mauritania' 'Mauritius' 'Mexico'\n",
      " 'Micronesia (Federated States of)' 'Moldova' 'Monaco' 'Mongolia'\n",
      " 'Montenegro' 'Morocco' 'Mozambique' 'Myanmar' 'Namibia' 'Nauru' 'Nepal'\n",
      " 'Netherlands' 'Netherlands Antilles' 'New Caledonia' 'New Zealand'\n",
      " 'Nicaragua' 'Niger' 'Nigeria' 'Niue' 'Norway' 'Oman' 'Pakistan' 'Palau'\n",
      " 'Panama' 'Papua New Guinea' 'Paraguay' 'Peru' 'Philippines' 'Poland'\n",
      " 'Portugal' 'Puerto Rico' 'Qatar' 'Romania' 'Russia' 'Rwanda'\n",
      " 'Saint Kitts and Nevis' 'Saint Lucia' 'Saint Vincent and the Grenadines'\n",
      " 'Samoa' 'San Marino' 'Sao Tome and Principe' 'Saudi Arabia' 'Senegal'\n",
      " 'Serbia' 'Seychelles' 'Sierra Leone' 'Singapore' 'Slovakia' 'Slovenia'\n",
      " 'Solomon Islands' 'Somalia' 'South Africa' 'Spain' 'Sri Lanka' 'Sudan'\n",
      " 'Suriname' 'Swaziland' 'Sweden' 'Switzerland' 'Syria' 'Taiwan'\n",
      " 'Tajikistan' 'Tanzania' 'Thailand' 'Timor-Leste' 'Togo' 'Tonga'\n",
      " 'Trinidad and Tobago' 'Tunisia' 'Turkey' 'Turkmenistan' 'Tuvalu' 'Uganda'\n",
      " 'Ukraine' 'United Arab Emirates' 'United Kingdom'\n",
      " 'United States of America' 'Uruguay' 'Uzbekistan' 'Vanuatu' 'Venezuela'\n",
      " 'Vietnam' 'West Bank and Gaza' 'Yemen' 'Zambia' 'Zimbabwe']\n",
      "Name:\n",
      " Country\n"
     ]
    }
   ],
   "source": [
    "print(\"Shape:\\n\", country_col.shape)\n",
    "print(\"Index:\\n\", country_col.index)\n",
    "print(\"Values:\\n\", country_col.values)\n",
    "print(\"Name:\\n\", country_col.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Series的切片仍然是Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last 2 countries:\n",
      " 200      Zambia\n",
      "201    Zimbabwe\n",
      "Name: Country, dtype: object\n",
      "Last 2 countries type:\n",
      " <class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "print(\"Last 2 countries:\\n\", country_col[-2:])\n",
    "print(\"Last 2 countries type:\\n\", type(country_col[-2:]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Numpy的函数也可以对DataFrame和Series对象使用\n",
    "- DataFrame, Series和Numpy数组ndarray之间可以进行算数运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean of last col:\n",
      " 34099.64021164021\n",
      "Series minus ndarray:\n",
      " 0      0.0\n",
      "1      0.0\n",
      "2      0.0\n",
      "3      0.0\n",
      "4      0.0\n",
      "5      0.0\n",
      "6      0.0\n",
      "7      0.0\n",
      "8      0.0\n",
      "9      0.0\n",
      "10     0.0\n",
      "11     0.0\n",
      "12     0.0\n",
      "13     0.0\n",
      "14     0.0\n",
      "15     0.0\n",
      "16     0.0\n",
      "17     0.0\n",
      "18     0.0\n",
      "19     NaN\n",
      "20     0.0\n",
      "21     0.0\n",
      "22     0.0\n",
      "23     0.0\n",
      "24     0.0\n",
      "25     0.0\n",
      "26     0.0\n",
      "27     0.0\n",
      "28     0.0\n",
      "29     0.0\n",
      "      ... \n",
      "172    0.0\n",
      "173    0.0\n",
      "174    0.0\n",
      "175    0.0\n",
      "176    NaN\n",
      "177    0.0\n",
      "178    0.0\n",
      "179    0.0\n",
      "180    0.0\n",
      "181    0.0\n",
      "182    0.0\n",
      "183    0.0\n",
      "184    0.0\n",
      "185    0.0\n",
      "186    0.0\n",
      "187    0.0\n",
      "188    0.0\n",
      "189    0.0\n",
      "190    0.0\n",
      "191    0.0\n",
      "192    0.0\n",
      "193    0.0\n",
      "194    0.0\n",
      "195    0.0\n",
      "196    0.0\n",
      "197    0.0\n",
      "198    NaN\n",
      "199    0.0\n",
      "200    0.0\n",
      "201    0.0\n",
      "Name: Population (in thousands) total, Length: 202, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "last_col = df.columns[-1]\n",
    "print(\"Mean of last col:\\n\", np.mean(df[last_col]))\n",
    "print(\"Series minus ndarray:\\n\", df[last_col]-df[last_col].values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query data in Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "head 2:\n",
      "                           Yearly mean total sunspot number  \\\n",
      "Gregorian calendar year                                      \n",
      "1700.5                                                 8.3   \n",
      "1701.5                                                18.3   \n",
      "\n",
      "                          Yearly mean standard deviation  \\\n",
      "Gregorian calendar year                                    \n",
      "1700.5                                              -1.0   \n",
      "1701.5                                              -1.0   \n",
      "\n",
      "                          Number of observations used  \\\n",
      "Gregorian calendar year                                 \n",
      "1700.5                                             -1   \n",
      "1701.5                                             -1   \n",
      "\n",
      "                          Definitive/provisional marker  \n",
      "Gregorian calendar year                                  \n",
      "1700.5                                                1  \n",
      "1701.5                                                1  \n",
      "tail 2:\n",
      "                           Yearly mean total sunspot number  \\\n",
      "Gregorian calendar year                                      \n",
      "2016.5                                                39.8   \n",
      "2017.5                                                21.7   \n",
      "\n",
      "                          Yearly mean standard deviation  \\\n",
      "Gregorian calendar year                                    \n",
      "2016.5                                               3.9   \n",
      "2017.5                                               2.5   \n",
      "\n",
      "                          Number of observations used  \\\n",
      "Gregorian calendar year                                 \n",
      "2016.5                                           9940   \n",
      "2017.5                                          11444   \n",
      "\n",
      "                          Definitive/provisional marker  \n",
      "Gregorian calendar year                                  \n",
      "2016.5                                                1  \n",
      "2017.5                                                1  \n"
     ]
    }
   ],
   "source": [
    "sunspots = read_csv('SN_y_tot_V2.0.csv', index_col = 0)\n",
    "print('head 2:\\n', sunspots.head(2))\n",
    "print('tail 2:\\n', sunspots.tail(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last value:   Yearly mean total sunspot number       21.7\n",
      " Yearly mean standard deviation          2.5\n",
      " Number of observations used         11444.0\n",
      " Definitive/provisional marker           1.0\n",
      "Name: 2017.5, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "last_data = sunspots.index[-1]\n",
    "print(\"Last value: \", sunspots.loc[last_data])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Float64Index([1700.5, 1701.5, 1702.5, 1703.5, 1704.5, 1705.5, 1706.5, 1707.5,\n",
       "              1708.5, 1709.5,\n",
       "              ...\n",
       "              2008.5, 2009.5, 2010.5, 2011.5, 2012.5, 2013.5, 2014.5, 2015.5,\n",
       "              2016.5, 2017.5],\n",
       "             dtype='float64', name='Gregorian calendar year', length=318)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sunspots.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 可以按索引范围查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Values slicing by index:\n",
      "                           Yearly mean total sunspot number  \\\n",
      "Gregorian calendar year                                      \n",
      "2000.5                                               173.9   \n",
      "2001.5                                               170.4   \n",
      "2002.5                                               163.6   \n",
      "2003.5                                                99.3   \n",
      "2004.5                                                65.3   \n",
      "2005.5                                                45.8   \n",
      "2006.5                                                24.7   \n",
      "2007.5                                                12.6   \n",
      "2008.5                                                 4.2   \n",
      "2009.5                                                 4.8   \n",
      "2010.5                                                24.9   \n",
      "2011.5                                                80.8   \n",
      "2012.5                                                84.5   \n",
      "2013.5                                                94.0   \n",
      "2014.5                                               113.3   \n",
      "2015.5                                                69.8   \n",
      "2016.5                                                39.8   \n",
      "2017.5                                                21.7   \n",
      "\n",
      "                          Yearly mean standard deviation  \\\n",
      "Gregorian calendar year                                    \n",
      "2000.5                                              10.1   \n",
      "2001.5                                              10.5   \n",
      "2002.5                                               9.8   \n",
      "2003.5                                               7.1   \n",
      "2004.5                                               5.9   \n",
      "2005.5                                               4.7   \n",
      "2006.5                                               3.5   \n",
      "2007.5                                               2.7   \n",
      "2008.5                                               2.5   \n",
      "2009.5                                               2.5   \n",
      "2010.5                                               3.4   \n",
      "2011.5                                               6.7   \n",
      "2012.5                                               6.7   \n",
      "2013.5                                               6.9   \n",
      "2014.5                                               8.0   \n",
      "2015.5                                               6.4   \n",
      "2016.5                                               3.9   \n",
      "2017.5                                               2.5   \n",
      "\n",
      "                          Number of observations used  \\\n",
      "Gregorian calendar year                                 \n",
      "2000.5                                           5953   \n",
      "2001.5                                           6558   \n",
      "2002.5                                           6588   \n",
      "2003.5                                           7087   \n",
      "2004.5                                           6882   \n",
      "2005.5                                           7084   \n",
      "2006.5                                           6370   \n",
      "2007.5                                           6841   \n",
      "2008.5                                           6644   \n",
      "2009.5                                           6465   \n",
      "2010.5                                           6328   \n",
      "2011.5                                           6077   \n",
      "2012.5                                           5753   \n",
      "2013.5                                           5347   \n",
      "2014.5                                           5273   \n",
      "2015.5                                           8903   \n",
      "2016.5                                           9940   \n",
      "2017.5                                          11444   \n",
      "\n",
      "                          Definitive/provisional marker  \n",
      "Gregorian calendar year                                  \n",
      "2000.5                                                1  \n",
      "2001.5                                                1  \n",
      "2002.5                                                1  \n",
      "2003.5                                                1  \n",
      "2004.5                                                1  \n",
      "2005.5                                                1  \n",
      "2006.5                                                1  \n",
      "2007.5                                                1  \n",
      "2008.5                                                1  \n",
      "2009.5                                                1  \n",
      "2010.5                                                1  \n",
      "2011.5                                                1  \n",
      "2012.5                                                1  \n",
      "2013.5                                                1  \n",
      "2014.5                                                1  \n",
      "2015.5                                                1  \n",
      "2016.5                                                1  \n",
      "2017.5                                                1  \n"
     ]
    }
   ],
   "source": [
    "print(\"Values slicing by index:\\n\", sunspots[2000.5:2018.5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 也可以用一组下标索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Slicing from a list of indices:\n",
      "                           Yearly mean total sunspot number  \\\n",
      "Gregorian calendar year                                      \n",
      "1702.5                                                26.7   \n",
      "1704.5                                                60.0   \n",
      "2017.5                                                21.7   \n",
      "2016.5                                                39.8   \n",
      "\n",
      "                          Yearly mean standard deviation  \\\n",
      "Gregorian calendar year                                    \n",
      "1702.5                                              -1.0   \n",
      "1704.5                                              -1.0   \n",
      "2017.5                                               2.5   \n",
      "2016.5                                               3.9   \n",
      "\n",
      "                          Number of observations used  \\\n",
      "Gregorian calendar year                                 \n",
      "1702.5                                             -1   \n",
      "1704.5                                             -1   \n",
      "2017.5                                          11444   \n",
      "2016.5                                           9940   \n",
      "\n",
      "                          Definitive/provisional marker  \n",
      "Gregorian calendar year                                  \n",
      "1702.5                                                1  \n",
      "1704.5                                                1  \n",
      "2017.5                                                1  \n",
      "2016.5                                                1  \n"
     ]
    }
   ],
   "source": [
    "print(\"Slicing from a list of indices:\\n\", sunspots.iloc[[2, 4, -1, -2]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 有2种方式选取特定数据：iloc和iat，iat更快\n",
    "传2个参数，分别是行序号和列序号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Scalar with iloc:\n",
      " 21.7\n",
      "Scalar with iat:\n",
      " 21.7\n"
     ]
    }
   ],
   "source": [
    "print(\"Scalar with iloc:\\n\", sunspots.iloc[-1,0])\n",
    "print(\"Scalar with iat:\\n\", sunspots.iat[-1,0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### loc和iloc\n",
    "- loc的参数是标签名，iloc的参数是从0开始的编号"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用布尔值选取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Yearly mean total sunspot number</th>\n",
       "      <th>Yearly mean standard deviation</th>\n",
       "      <th>Number of observations used</th>\n",
       "      <th>Definitive/provisional marker</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gregorian calendar year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1705.5</th>\n",
       "      <td>96.7</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1717.5</th>\n",
       "      <td>105.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1718.5</th>\n",
       "      <td>100.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1726.5</th>\n",
       "      <td>130.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1727.5</th>\n",
       "      <td>203.3</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1728.5</th>\n",
       "      <td>171.7</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1729.5</th>\n",
       "      <td>121.7</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1736.5</th>\n",
       "      <td>116.7</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1737.5</th>\n",
       "      <td>135.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1738.5</th>\n",
       "      <td>185.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1739.5</th>\n",
       "      <td>168.3</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1740.5</th>\n",
       "      <td>121.7</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1748.5</th>\n",
       "      <td>100.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1749.5</th>\n",
       "      <td>134.8</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1750.5</th>\n",
       "      <td>139.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1751.5</th>\n",
       "      <td>79.5</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1752.5</th>\n",
       "      <td>79.7</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1758.5</th>\n",
       "      <td>79.3</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1759.5</th>\n",
       "      <td>90.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1760.5</th>\n",
       "      <td>104.8</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1761.5</th>\n",
       "      <td>143.2</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1762.5</th>\n",
       "      <td>102.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1768.5</th>\n",
       "      <td>116.3</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1769.5</th>\n",
       "      <td>176.8</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1770.5</th>\n",
       "      <td>168.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1771.5</th>\n",
       "      <td>136.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772.5</th>\n",
       "      <td>110.8</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1777.5</th>\n",
       "      <td>154.2</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1778.5</th>\n",
       "      <td>257.3</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1779.5</th>\n",
       "      <td>209.8</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1958.5</th>\n",
       "      <td>261.7</td>\n",
       "      <td>10.8</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959.5</th>\n",
       "      <td>225.1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960.5</th>\n",
       "      <td>159.0</td>\n",
       "      <td>8.4</td>\n",
       "      <td>366</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1967.5</th>\n",
       "      <td>132.9</td>\n",
       "      <td>7.7</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968.5</th>\n",
       "      <td>150.0</td>\n",
       "      <td>8.2</td>\n",
       "      <td>366</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1969.5</th>\n",
       "      <td>149.4</td>\n",
       "      <td>8.2</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970.5</th>\n",
       "      <td>148.0</td>\n",
       "      <td>8.1</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971.5</th>\n",
       "      <td>94.4</td>\n",
       "      <td>6.5</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1972.5</th>\n",
       "      <td>97.6</td>\n",
       "      <td>6.6</td>\n",
       "      <td>366</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978.5</th>\n",
       "      <td>131.0</td>\n",
       "      <td>7.6</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1979.5</th>\n",
       "      <td>220.1</td>\n",
       "      <td>9.9</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980.5</th>\n",
       "      <td>218.9</td>\n",
       "      <td>9.9</td>\n",
       "      <td>366</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981.5</th>\n",
       "      <td>198.9</td>\n",
       "      <td>13.1</td>\n",
       "      <td>3049</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982.5</th>\n",
       "      <td>162.4</td>\n",
       "      <td>12.1</td>\n",
       "      <td>3436</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983.5</th>\n",
       "      <td>91.0</td>\n",
       "      <td>7.6</td>\n",
       "      <td>4216</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988.5</th>\n",
       "      <td>123.0</td>\n",
       "      <td>8.4</td>\n",
       "      <td>6556</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989.5</th>\n",
       "      <td>211.1</td>\n",
       "      <td>12.8</td>\n",
       "      <td>6932</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990.5</th>\n",
       "      <td>191.8</td>\n",
       "      <td>11.2</td>\n",
       "      <td>7108</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991.5</th>\n",
       "      <td>203.3</td>\n",
       "      <td>12.7</td>\n",
       "      <td>6932</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992.5</th>\n",
       "      <td>133.0</td>\n",
       "      <td>8.9</td>\n",
       "      <td>7845</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998.5</th>\n",
       "      <td>88.3</td>\n",
       "      <td>6.6</td>\n",
       "      <td>6353</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999.5</th>\n",
       "      <td>136.3</td>\n",
       "      <td>9.3</td>\n",
       "      <td>6413</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000.5</th>\n",
       "      <td>173.9</td>\n",
       "      <td>10.1</td>\n",
       "      <td>5953</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001.5</th>\n",
       "      <td>170.4</td>\n",
       "      <td>10.5</td>\n",
       "      <td>6558</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002.5</th>\n",
       "      <td>163.6</td>\n",
       "      <td>9.8</td>\n",
       "      <td>6588</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003.5</th>\n",
       "      <td>99.3</td>\n",
       "      <td>7.1</td>\n",
       "      <td>7087</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011.5</th>\n",
       "      <td>80.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>6077</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012.5</th>\n",
       "      <td>84.5</td>\n",
       "      <td>6.7</td>\n",
       "      <td>5753</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.5</th>\n",
       "      <td>94.0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>5347</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.5</th>\n",
       "      <td>113.3</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5273</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>135 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Yearly mean total sunspot number  \\\n",
       "Gregorian calendar year                                      \n",
       "1705.5                                                96.7   \n",
       "1717.5                                               105.0   \n",
       "1718.5                                               100.0   \n",
       "1726.5                                               130.0   \n",
       "1727.5                                               203.3   \n",
       "1728.5                                               171.7   \n",
       "1729.5                                               121.7   \n",
       "1736.5                                               116.7   \n",
       "1737.5                                               135.0   \n",
       "1738.5                                               185.0   \n",
       "1739.5                                               168.3   \n",
       "1740.5                                               121.7   \n",
       "1748.5                                               100.0   \n",
       "1749.5                                               134.8   \n",
       "1750.5                                               139.0   \n",
       "1751.5                                                79.5   \n",
       "1752.5                                                79.7   \n",
       "1758.5                                                79.3   \n",
       "1759.5                                                90.0   \n",
       "1760.5                                               104.8   \n",
       "1761.5                                               143.2   \n",
       "1762.5                                               102.0   \n",
       "1768.5                                               116.3   \n",
       "1769.5                                               176.8   \n",
       "1770.5                                               168.0   \n",
       "1771.5                                               136.0   \n",
       "1772.5                                               110.8   \n",
       "1777.5                                               154.2   \n",
       "1778.5                                               257.3   \n",
       "1779.5                                               209.8   \n",
       "...                                                    ...   \n",
       "1958.5                                               261.7   \n",
       "1959.5                                               225.1   \n",
       "1960.5                                               159.0   \n",
       "1967.5                                               132.9   \n",
       "1968.5                                               150.0   \n",
       "1969.5                                               149.4   \n",
       "1970.5                                               148.0   \n",
       "1971.5                                                94.4   \n",
       "1972.5                                                97.6   \n",
       "1978.5                                               131.0   \n",
       "1979.5                                               220.1   \n",
       "1980.5                                               218.9   \n",
       "1981.5                                               198.9   \n",
       "1982.5                                               162.4   \n",
       "1983.5                                                91.0   \n",
       "1988.5                                               123.0   \n",
       "1989.5                                               211.1   \n",
       "1990.5                                               191.8   \n",
       "1991.5                                               203.3   \n",
       "1992.5                                               133.0   \n",
       "1998.5                                                88.3   \n",
       "1999.5                                               136.3   \n",
       "2000.5                                               173.9   \n",
       "2001.5                                               170.4   \n",
       "2002.5                                               163.6   \n",
       "2003.5                                                99.3   \n",
       "2011.5                                                80.8   \n",
       "2012.5                                                84.5   \n",
       "2013.5                                                94.0   \n",
       "2014.5                                               113.3   \n",
       "\n",
       "                          Yearly mean standard deviation  \\\n",
       "Gregorian calendar year                                    \n",
       "1705.5                                              -1.0   \n",
       "1717.5                                              -1.0   \n",
       "1718.5                                              -1.0   \n",
       "1726.5                                              -1.0   \n",
       "1727.5                                              -1.0   \n",
       "1728.5                                              -1.0   \n",
       "1729.5                                              -1.0   \n",
       "1736.5                                              -1.0   \n",
       "1737.5                                              -1.0   \n",
       "1738.5                                              -1.0   \n",
       "1739.5                                              -1.0   \n",
       "1740.5                                              -1.0   \n",
       "1748.5                                              -1.0   \n",
       "1749.5                                              -1.0   \n",
       "1750.5                                              -1.0   \n",
       "1751.5                                              -1.0   \n",
       "1752.5                                              -1.0   \n",
       "1758.5                                              -1.0   \n",
       "1759.5                                              -1.0   \n",
       "1760.5                                              -1.0   \n",
       "1761.5                                              -1.0   \n",
       "1762.5                                              -1.0   \n",
       "1768.5                                              -1.0   \n",
       "1769.5                                              -1.0   \n",
       "1770.5                                              -1.0   \n",
       "1771.5                                              -1.0   \n",
       "1772.5                                              -1.0   \n",
       "1777.5                                              -1.0   \n",
       "1778.5                                              -1.0   \n",
       "1779.5                                              -1.0   \n",
       "...                                                  ...   \n",
       "1958.5                                              10.8   \n",
       "1959.5                                              10.0   \n",
       "1960.5                                               8.4   \n",
       "1967.5                                               7.7   \n",
       "1968.5                                               8.2   \n",
       "1969.5                                               8.2   \n",
       "1970.5                                               8.1   \n",
       "1971.5                                               6.5   \n",
       "1972.5                                               6.6   \n",
       "1978.5                                               7.6   \n",
       "1979.5                                               9.9   \n",
       "1980.5                                               9.9   \n",
       "1981.5                                              13.1   \n",
       "1982.5                                              12.1   \n",
       "1983.5                                               7.6   \n",
       "1988.5                                               8.4   \n",
       "1989.5                                              12.8   \n",
       "1990.5                                              11.2   \n",
       "1991.5                                              12.7   \n",
       "1992.5                                               8.9   \n",
       "1998.5                                               6.6   \n",
       "1999.5                                               9.3   \n",
       "2000.5                                              10.1   \n",
       "2001.5                                              10.5   \n",
       "2002.5                                               9.8   \n",
       "2003.5                                               7.1   \n",
       "2011.5                                               6.7   \n",
       "2012.5                                               6.7   \n",
       "2013.5                                               6.9   \n",
       "2014.5                                               8.0   \n",
       "\n",
       "                          Number of observations used  \\\n",
       "Gregorian calendar year                                 \n",
       "1705.5                                             -1   \n",
       "1717.5                                             -1   \n",
       "1718.5                                             -1   \n",
       "1726.5                                             -1   \n",
       "1727.5                                             -1   \n",
       "1728.5                                             -1   \n",
       "1729.5                                             -1   \n",
       "1736.5                                             -1   \n",
       "1737.5                                             -1   \n",
       "1738.5                                             -1   \n",
       "1739.5                                             -1   \n",
       "1740.5                                             -1   \n",
       "1748.5                                             -1   \n",
       "1749.5                                             -1   \n",
       "1750.5                                             -1   \n",
       "1751.5                                             -1   \n",
       "1752.5                                             -1   \n",
       "1758.5                                             -1   \n",
       "1759.5                                             -1   \n",
       "1760.5                                             -1   \n",
       "1761.5                                             -1   \n",
       "1762.5                                             -1   \n",
       "1768.5                                             -1   \n",
       "1769.5                                             -1   \n",
       "1770.5                                             -1   \n",
       "1771.5                                             -1   \n",
       "1772.5                                             -1   \n",
       "1777.5                                             -1   \n",
       "1778.5                                             -1   \n",
       "1779.5                                             -1   \n",
       "...                                               ...   \n",
       "1958.5                                            365   \n",
       "1959.5                                            365   \n",
       "1960.5                                            366   \n",
       "1967.5                                            365   \n",
       "1968.5                                            366   \n",
       "1969.5                                            365   \n",
       "1970.5                                            365   \n",
       "1971.5                                            365   \n",
       "1972.5                                            366   \n",
       "1978.5                                            365   \n",
       "1979.5                                            365   \n",
       "1980.5                                            366   \n",
       "1981.5                                           3049   \n",
       "1982.5                                           3436   \n",
       "1983.5                                           4216   \n",
       "1988.5                                           6556   \n",
       "1989.5                                           6932   \n",
       "1990.5                                           7108   \n",
       "1991.5                                           6932   \n",
       "1992.5                                           7845   \n",
       "1998.5                                           6353   \n",
       "1999.5                                           6413   \n",
       "2000.5                                           5953   \n",
       "2001.5                                           6558   \n",
       "2002.5                                           6588   \n",
       "2003.5                                           7087   \n",
       "2011.5                                           6077   \n",
       "2012.5                                           5753   \n",
       "2013.5                                           5347   \n",
       "2014.5                                           5273   \n",
       "\n",
       "                          Definitive/provisional marker  \n",
       "Gregorian calendar year                                  \n",
       "1705.5                                                1  \n",
       "1717.5                                                1  \n",
       "1718.5                                                1  \n",
       "1726.5                                                1  \n",
       "1727.5                                                1  \n",
       "1728.5                                                1  \n",
       "1729.5                                                1  \n",
       "1736.5                                                1  \n",
       "1737.5                                                1  \n",
       "1738.5                                                1  \n",
       "1739.5                                                1  \n",
       "1740.5                                                1  \n",
       "1748.5                                                1  \n",
       "1749.5                                                1  \n",
       "1750.5                                                1  \n",
       "1751.5                                                1  \n",
       "1752.5                                                1  \n",
       "1758.5                                                1  \n",
       "1759.5                                                1  \n",
       "1760.5                                                1  \n",
       "1761.5                                                1  \n",
       "1762.5                                                1  \n",
       "1768.5                                                1  \n",
       "1769.5                                                1  \n",
       "1770.5                                                1  \n",
       "1771.5                                                1  \n",
       "1772.5                                                1  \n",
       "1777.5                                                1  \n",
       "1778.5                                                1  \n",
       "1779.5                                                1  \n",
       "...                                                 ...  \n",
       "1958.5                                                1  \n",
       "1959.5                                                1  \n",
       "1960.5                                                1  \n",
       "1967.5                                                1  \n",
       "1968.5                                                1  \n",
       "1969.5                                                1  \n",
       "1970.5                                                1  \n",
       "1971.5                                                1  \n",
       "1972.5                                                1  \n",
       "1978.5                                                1  \n",
       "1979.5                                                1  \n",
       "1980.5                                                1  \n",
       "1981.5                                                1  \n",
       "1982.5                                                1  \n",
       "1983.5                                                1  \n",
       "1988.5                                                1  \n",
       "1989.5                                                1  \n",
       "1990.5                                                1  \n",
       "1991.5                                                1  \n",
       "1992.5                                                1  \n",
       "1998.5                                                1  \n",
       "1999.5                                                1  \n",
       "2000.5                                                1  \n",
       "2001.5                                                1  \n",
       "2002.5                                                1  \n",
       "2003.5                                                1  \n",
       "2011.5                                                1  \n",
       "2012.5                                                1  \n",
       "2013.5                                                1  \n",
       "2014.5                                                1  \n",
       "\n",
       "[135 rows x 4 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_col = sunspots.columns[0]\n",
    "sunspots[sunspots[first_col] > np.mean(sunspots.iloc[:,0])]\n",
    "# 或者sunspots[sunspots.iloc[:,0] > np.mean(sunspots.iloc[:,0])]，结果是一样的"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Statistics with Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Yearly mean total sunspot number</th>\n",
       "      <th>Yearly mean standard deviation</th>\n",
       "      <th>Number of observations used</th>\n",
       "      <th>Definitive/provisional marker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>318.000000</td>\n",
       "      <td>318.000000</td>\n",
       "      <td>318.000000</td>\n",
       "      <td>318.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>79.196855</td>\n",
       "      <td>4.649057</td>\n",
       "      <td>954.069182</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>61.985539</td>\n",
       "      <td>5.295702</td>\n",
       "      <td>2155.041512</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>24.950000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>66.250000</td>\n",
       "      <td>4.200000</td>\n",
       "      <td>365.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>116.025000</td>\n",
       "      <td>8.900000</td>\n",
       "      <td>365.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>269.300000</td>\n",
       "      <td>19.100000</td>\n",
       "      <td>11444.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Yearly mean total sunspot number   Yearly mean standard deviation  \\\n",
       "count                         318.000000                       318.000000   \n",
       "mean                           79.196855                         4.649057   \n",
       "std                            61.985539                         5.295702   \n",
       "min                             0.000000                        -1.000000   \n",
       "25%                            24.950000                        -1.000000   \n",
       "50%                            66.250000                         4.200000   \n",
       "75%                           116.025000                         8.900000   \n",
       "max                           269.300000                        19.100000   \n",
       "\n",
       "        Number of observations used   Definitive/provisional marker  \n",
       "count                    318.000000                           318.0  \n",
       "mean                     954.069182                             1.0  \n",
       "std                     2155.041512                             0.0  \n",
       "min                       -1.000000                             1.0  \n",
       "25%                       -1.000000                             1.0  \n",
       "50%                      365.000000                             1.0  \n",
       "75%                      365.000000                             1.0  \n",
       "max                    11444.000000                             1.0  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sunspots.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data aggregation with Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Food  Number     Price Weather\n",
      "0       soup       8  4.076870    cold\n",
      "1   icecream       4  0.553660     hot\n",
      "2  chocolate       4  7.885349    cold\n",
      "3   icecream       4  2.873052     hot\n",
      "4       soup       2  4.503506    cold\n",
      "5       soup       1  3.039123     hot\n",
      "6   icecream       2  5.263995    cold\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from numpy.random import seed\n",
    "from numpy.random import rand\n",
    "from numpy.random import randint\n",
    "import numpy as np\n",
    "\n",
    "seed(40)\n",
    "\n",
    "df = pd.DataFrame({'Weather': ['cold', 'hot', 'cold', 'hot', 'cold', 'hot', 'cold'],\n",
    "                   'Food': ['soup', 'icecream', 'chocolate', 'icecream', 'soup', 'soup','icecream'],\n",
    "                   'Price': 10*rand(7),\n",
    "                   'Number': randint(1, 9, size=(7,))})\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Type of weather_group:\n",
      " <class 'pandas.core.groupby.DataFrameGroupBy'>\n",
      "Group 1 cold\n",
      "        Food  Number     Price Weather\n",
      "0       soup       8  4.076870    cold\n",
      "2  chocolate       4  7.885349    cold\n",
      "4       soup       2  4.503506    cold\n",
      "6   icecream       2  5.263995    cold\n",
      "Group 2 hot\n",
      "       Food  Number     Price Weather\n",
      "1  icecream       4  0.553660     hot\n",
      "3  icecream       4  2.873052     hot\n",
      "5      soup       1  3.039123     hot\n"
     ]
    }
   ],
   "source": [
    "weather_group = df.groupby('Weather')\n",
    "print(\"Type of weather_group:\\n\", type(weather_group))\n",
    "i = 0\n",
    "for name, group in weather_group:\n",
    "    i += 1\n",
    "    print(\"Group\", i, name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WF groups {('cold', 'chocolate'): Int64Index([2], dtype='int64'), ('cold', 'icecream'): Int64Index([6], dtype='int64'), ('cold', 'soup'): Int64Index([0, 4], dtype='int64'), ('hot', 'icecream'): Int64Index([1, 3], dtype='int64'), ('hot', 'soup'): Int64Index([5], dtype='int64')}\n"
     ]
    }
   ],
   "source": [
    "wf_group = df.groupby(['Weather', 'Food'])\n",
    "print(\"WF groups\", wf_group.groups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WF aggregated\n",
      "                   Number            Price          \n",
      "                    mean median      mean    median\n",
      "Weather Food                                       \n",
      "cold    chocolate      4      4  7.885349  7.885349\n",
      "        icecream       2      2  5.263995  5.263995\n",
      "        soup           5      5  4.290188  4.290188\n",
      "hot     icecream       4      4  1.713356  1.713356\n",
      "        soup           1      1  3.039123  3.039123\n"
     ]
    }
   ],
   "source": [
    "print(\"WF aggregated\\n\", wf_group.agg([np.mean, np.median]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenating and appending DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df: 3\n",
      "         Food  Number     Price Weather\n",
      "0       soup       8  4.076870    cold\n",
      "1   icecream       4  0.553660     hot\n",
      "2  chocolate       4  7.885349    cold\n"
     ]
    }
   ],
   "source": [
    "print(\"df: 3\\n\", df[0:3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Food  Number     Price Weather\n",
      "0       soup       8  4.076870    cold\n",
      "1   icecream       4  0.553660     hot\n",
      "2  chocolate       4  7.885349    cold\n",
      "5       soup       1  3.039123     hot\n",
      "6   icecream       2  5.263995    cold\n"
     ]
    }
   ],
   "source": [
    "print(df[:3].append(df[5:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Food  Number     Price Weather\n",
      "0       soup       8  4.076870    cold\n",
      "1   icecream       4  0.553660     hot\n",
      "2  chocolate       4  7.885349    cold\n",
      "6   icecream       2  5.263995    cold\n"
     ]
    }
   ],
   "source": [
    "print(pd.concat([df[0:3], df[6:]]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Joining DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Destination  Employ ID\n",
      "0      Hankou          5\n",
      "1     Hanyang          3\n",
      "2     Wuchang          9\n",
      "   Employ ID  Tips\n",
      "0          5  10.0\n",
      "1          9   5.0\n",
      "2          7   2.5\n"
     ]
    }
   ],
   "source": [
    "dest = pd.DataFrame({\"Employ ID\": [5, 3, 9],\n",
    "                     \"Destination\": ['Hankou', 'Hanyang', 'Wuchang']})\n",
    "tips = pd.DataFrame({\"Employ ID\": [5, 9, 7],\n",
    "                     \"Tips\": [10, 5, 2.5]})\n",
    "print(dest)\n",
    "print(tips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Merge on Employ ID\n",
      "   Destination  Employ ID  Tips\n",
      "0      Hankou          5  10.0\n",
      "1     Wuchang          9   5.0\n"
     ]
    }
   ],
   "source": [
    "print(\"Merge on Employ ID\\n\", pd.merge(dest, tips, on = \"Employ ID\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inner join with merge\n",
      "   Destination  Employ ID  Tips\n",
      "0      Hankou          5  10.0\n",
      "1     Wuchang          9   5.0\n"
     ]
    }
   ],
   "source": [
    "print(\"Inner join with merge\\n\", pd.merge(dest, tips, how=\"inner\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inner join with merge\n",
      "   Destination  Employ ID  Tips\n",
      "0      Hankou          5  10.0\n",
      "1     Hanyang          3   NaN\n",
      "2     Wuchang          9   5.0\n",
      "3         NaN          7   2.5\n"
     ]
    }
   ],
   "source": [
    "print(\"Inner join with merge\\n\", pd.merge(dest, tips, how=\"outer\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Country  Net primary school enrolment ratio male (%)\n",
      "0  Afghanistan                                          NaN\n",
      "1      Albania                                         94.0\n"
     ]
    }
   ],
   "source": [
    "from pandas.io.parsers import read_csv\n",
    "\n",
    "df = read_csv(\"WHO_first9cols.csv\")\n",
    "df = df[['Country', df.columns[-2]]][:2]\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Country  Net primary school enrolment ratio male (%)\n",
      "0    False                                         True\n",
      "1    False                                        False\n"
     ]
    }
   ],
   "source": [
    "print(pd.isnull(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Country  Net primary school enrolment ratio male (%)\n",
      "0  Afghanistan                                          0.0\n",
      "1      Albania                                         94.0\n"
     ]
    }
   ],
   "source": [
    "df[df.columns[-1]] = df[df.columns[-1]].replace(np.nan, 0)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 替换指定列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Country  Net primary school enrolment ratio male (%)\n",
      "0  Afghanistan                                          0.0\n",
      "1      Albania                                         94.0\n"
     ]
    }
   ],
   "source": [
    "values = {df.columns[-1]: -1}\n",
    "df.fillna(value = values, inplace = True)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2017-02-12', '2017-02-19', '2017-02-26', '2017-03-05',\n",
      "               '2017-03-12', '2017-03-19', '2017-03-26', '2017-04-02',\n",
      "               '2017-04-09', '2017-04-16', '2017-04-23', '2017-04-30',\n",
      "               '2017-05-07', '2017-05-14', '2017-05-21', '2017-05-28',\n",
      "               '2017-06-04', '2017-06-11', '2017-06-18', '2017-06-25',\n",
      "               '2017-07-02', '2017-07-09', '2017-07-16', '2017-07-23',\n",
      "               '2017-07-30', '2017-08-06', '2017-08-13', '2017-08-20',\n",
      "               '2017-08-27', '2017-09-03', '2017-09-10', '2017-09-17',\n",
      "               '2017-09-24', '2017-10-01', '2017-10-08', '2017-10-15',\n",
      "               '2017-10-22', '2017-10-29', '2017-11-05', '2017-11-12',\n",
      "               '2017-11-19', '2017-11-26', '2017-12-03', '2017-12-10',\n",
      "               '2017-12-17', '2017-12-24', '2017-12-31', '2018-01-07',\n",
      "               '2018-01-14', '2018-01-21', '2018-01-28', '2018-02-04',\n",
      "               '2018-02-11', '2018-02-18', '2018-02-25'],\n",
      "              dtype='datetime64[ns]', freq='W-SUN')\n"
     ]
    }
   ],
   "source": [
    "print(pd.date_range('2/8/2017', periods=55, freq='W'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2007-10-21', '2018-09-21'], dtype='datetime64[ns]', freq=None)\n"
     ]
    }
   ],
   "source": [
    "print(pd.to_datetime(['20071021', '20180921'], format='%Y%m%d'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Food  Number     Price Weather\n",
      "0       soup       8  4.076870    cold\n",
      "1   icecream       4  0.553660     hot\n",
      "2  chocolate       4  7.885349    cold\n",
      "3   icecream       4  2.873052     hot\n",
      "4       soup       2  4.503506    cold\n",
      "5       soup       1  3.039123     hot\n",
      "6   icecream       2  5.263995    cold\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from numpy.random import seed\n",
    "from numpy.random import rand\n",
    "from numpy.random import randint\n",
    "import numpy as np\n",
    "\n",
    "seed(40)\n",
    "\n",
    "df = pd.DataFrame({'Weather': ['cold', 'hot', 'cold', 'hot', 'cold', 'hot', 'cold'],\n",
    "                   'Food': ['soup', 'icecream', 'chocolate', 'icecream', 'soup', 'soup','icecream'],\n",
    "                   'Price': 10*rand(7),\n",
    "                   'Number': randint(1, 9, size=(7,))})\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Food     chocolate  icecream      soup\n",
      "Weather                               \n",
      "cold      7.885349  5.263995  4.290188\n",
      "hot            NaN  1.713356  3.039123\n"
     ]
    }
   ],
   "source": [
    "print(pd.pivot_table(df, values='Price', index='Weather', columns='Food', aggfunc=np.mean))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
